package org.ttjhome.db;

import java.util.Calendar;
import java.util.Date;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.persistence.criteria.SetJoin;

import org.ttjhome.db.dbo.Candidate;
import org.ttjhome.db.dbo.Candidate_;
import org.ttjhome.db.dbo.Tag;
import org.ttjhome.db.dbo.Tag_;
import org.ttjhome.html.struts.common.CandidateCriteria;
import org.ttjhome.util.StringUtils;

public class CandidateCriteriaHelper {

	
	public static CriteriaQuery<Candidate> getCandidateQuery(EntityManager entityManager,CandidateCriteria criteria)
	{
		CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
		CriteriaQuery<Candidate> criteriaQuery = criteriaBuilder
				.createQuery(Candidate.class);
		Root<Candidate> candidate = criteriaQuery.from(Candidate.class);
		
		Predicate predicate = null ;
		if (!StringUtils.isEmpty(criteria.getGender())) {
			if(predicate!=null)
				predicate=criteriaBuilder.and(predicate,criteriaBuilder.equal(candidate
					.get(Candidate_.gender), criteria.getGender()));
			else
				predicate=criteriaBuilder.and(criteriaBuilder.equal(candidate
						.get(Candidate_.gender), criteria.getGender()));
		}

		if (criteria.getBirthdayFrom()!=null)
		{
			if(predicate!=null)
				predicate=criteriaBuilder.and(predicate,criteriaBuilder.greaterThanOrEqualTo(candidate
					.get(Candidate_.birthDay), criteria.getBirthdayFrom()));
			else
				predicate=criteriaBuilder.and(criteriaBuilder.greaterThanOrEqualTo(candidate
						.get(Candidate_.birthDay), criteria.getBirthdayFrom()));
		}
		if (criteria.getBirthdayTo()!=null)
		{
			if(predicate!=null)
				predicate=criteriaBuilder.and(predicate,criteriaBuilder.lessThanOrEqualTo(candidate
					.get(Candidate_.birthDay), criteria.getBirthdayTo()));
			else
				predicate=criteriaBuilder.and(criteriaBuilder.lessThanOrEqualTo(candidate
						.get(Candidate_.birthDay), criteria.getBirthdayTo()));
		}
		if (criteria.getGraduateDateFrom()!=null)
		{
			if(predicate!=null)
				predicate=criteriaBuilder.and(predicate,criteriaBuilder.greaterThanOrEqualTo(candidate
					.get(Candidate_.workFrom), criteria.getGraduateDateFrom()));
			else
				predicate=criteriaBuilder.and(criteriaBuilder.greaterThanOrEqualTo(candidate
						.get(Candidate_.workFrom), criteria.getGraduateDateFrom()));
		}
		if (criteria.getGraduateDateTo()!=null)
		{
			if(predicate!=null)
				predicate=criteriaBuilder.and(predicate,criteriaBuilder.lessThanOrEqualTo(candidate
					.get(Candidate_.workFrom), criteria.getGraduateDateTo()));
			else
				predicate=criteriaBuilder.and(criteriaBuilder.lessThanOrEqualTo(candidate
						.get(Candidate_.workFrom), criteria.getGraduateDateTo()));
		}
		if(criteria.getTags()!=null&&criteria.getTags().size()>0)
		{
			SetJoin<Candidate, Tag> tagJoin = candidate.join(Candidate_.tags);
			Predicate tagPredicate=null;
			for(Tag tag:criteria.getTags()){
				if(tagPredicate!=null){
					tagPredicate = criteriaBuilder.or(tagPredicate,criteriaBuilder.or(criteriaBuilder
							.equal(tagJoin.get(Tag_.tag), tag.getTag())));
				}else{
					 tagPredicate = criteriaBuilder.or(criteriaBuilder
							.equal(tagJoin.get(Tag_.tag), tag.getTag()));
				}
				
			}
			
			if(predicate!=null)
				predicate = criteriaBuilder.and(predicate,tagPredicate);
			else
				predicate = tagPredicate;
		}
		
		if(predicate!=null)
			criteriaQuery.where(predicate).distinct(true);
		return criteriaQuery;
		
	}
	
	public static Query groupAllCandidateTags(EntityManager entityManager){
//		CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
//		CriteriaQuery<Tag> criteriaQuery = criteriaBuilder
//				.createQuery(Tag.class);
////		Root<Candidate> candidate = criteriaQuery.from(Candidate.class);
//		Root<Tag> tag = criteriaQuery.from(Tag.class);
//		SetJoin<Tag,Candidate > tagJoin = tag.join(Tag_.candidates,JoinType.LEFT);
//
//		return criteriaQuery.multiselect(tag.get(Tag_.tag), criteriaBuilder.count(tag.get(Tag_.tag)))
//		.groupBy(tag.get(Tag_.tag)).distinct(true);
	
		
		Query query = entityManager.createNativeQuery("select a.tag,count(*) from tags a, candidate_tags b, candidates c " +
				"where a.id =b.tag_id and b.candidate_id = c.id group by tag");
		return query;
	}
	
	public static void  resolveCriteriaType(String experienceType,CandidateCriteria criteria)
	{
		if(experienceType==null||experienceType.length()==0) return ;
		if(experienceType.equals("1")) {
			Calendar today = Calendar.getInstance();
			today.setTime(new Date());
			Calendar thatday = Calendar.getInstance();
			thatday.setTime(new Date());
			thatday.set(Calendar.YEAR, thatday.get(Calendar.YEAR)-3);
			criteria.setGraduateDateTo(today.getTime());
			criteria.setGraduateDateFrom(thatday.getTime());
		}
		else if(experienceType.equals("2")){
			Calendar thatday = Calendar.getInstance();
			thatday.setTime(new Date());
			thatday.set(Calendar.YEAR, thatday.get(Calendar.YEAR)-3);
			criteria.setGraduateDateTo(thatday.getTime());
		}
		else if(experienceType.equals("3")){
			Calendar thatday = Calendar.getInstance();
			thatday.setTime(new Date());
			thatday.set(Calendar.YEAR, thatday.get(Calendar.YEAR)-5);
			criteria.setGraduateDateTo(thatday.getTime());
		}else if(experienceType.equals("4")){
			Calendar thatday = Calendar.getInstance();
			thatday.setTime(new Date());
			thatday.set(Calendar.YEAR, thatday.get(Calendar.YEAR)-8);
			criteria.setGraduateDateTo(thatday.getTime());
		}else if(experienceType.equals("5")){
			Calendar thatday = Calendar.getInstance();
			thatday.setTime(new Date());
			thatday.set(Calendar.YEAR, thatday.get(Calendar.YEAR)-10);
			criteria.setGraduateDateTo(thatday.getTime());
		}
			
			
	}
}
